package com.example.sw_1.myapplication;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by SW-1 on 2018/7/9.
 */
public class jdbcUtil {

    /**
     * 获取数据库连接
     *
     * @return con
     */
    public static Connection getConn() {
        String driver = "com.mysql.jdbc.Driver";
        String url = "";
        String username = "";
        String password = "";
        Connection conn = null;
        try {
            Class.forName(driver); //classLoader,加载对应驱动
            conn = (Connection) DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 插入语句
     *
     * @param user UserModel 对象
     * @return i ;0 error ；1 sucess
     */
    public int insert(UserModel user) {
        System.out.println("---------" + user);
        Connection conn = getConn();
        System.out.println("conn---------" + conn);
        int i = 0;
        String sql = "insert into user (name,password,email) values(?,?,?)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, user.getEmail());
            i = pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    //upadte
    public int update(String sql) {
        Connection conn = getConn();
        int i = 0;
//        String sql = "update students set Age='" + student.getAge() + "' where Name='" + student.getName() + "'";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            System.out.println("resutl: " + i);
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    public List getAll(String sql) {
        Connection conn = getConn();
//        String sql = "select * from students";
        PreparedStatement pstmt;
        UserModel user = new UserModel();
        List<UserModel> list = new ArrayList<UserModel>();
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            //获取记录总数
            int col = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= col; i++) {
                    if (i == 1) {
                        user.setId(rs.getInt(i));
                    } else if (i == 2) {
                        user.setName(rs.getString(i));
                    } else if (i == 3) {
                        user.setPassword(rs.getString(i));
                    } else if (i == 4) {
                        user.setPhone(rs.getString(i) == null || rs.getString(i) == "" ? "" : rs.getString(i));
                    } else if (i == 4) {
                        user.setEmail(rs.getString(i));
                    }
                    list.add(user);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}